﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text.RegularExpressions;
using System.Configuration;


namespace PhoneBookConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            // Set log file name for debug
            string myLogTxtFile = "mylog.txt";
            string mylog = "";
            string mymsg = "";

            // Delete log file if it is more than 5MB. It will be recreated as new file.

            string myDirectory = AppDomain.CurrentDomain.BaseDirectory;
            string myFileLocationAndName = myDirectory + myLogTxtFile;
            FileInfo f = new FileInfo(myFileLocationAndName);
            long fSize = f.Length;
            if (fSize > 5242880) { File.Delete(myFileLocationAndName); }

            // Check if arguments are set for directory and xml file name (local and remote)
            string myXmlDirectory="";
            string myXmlFilename="";
            string myRemoteXmlDirectory ="";
            string myXmlLocalOutputFile ="PhoneBook.xml";
            string myXmlRemoteOutputFile = "";

            if (args != null && args.Length == 3)
            {
                //Console.WriteLine(args[0].ToString());
                myXmlDirectory = args[0].ToString();
                myXmlFilename = args[1].ToString();
                myRemoteXmlDirectory = args[2].ToString();
                mymsg = "Arguments are used.\r\nThese are the arguments:\r\nXmlLocalDirectory=" + myXmlDirectory + "\r\nXmlFilename=" + myXmlFilename + "\r\nXmlRemoteDirectory=" + myRemoteXmlDirectory + "\r\n";
                mylog = mymsg;
            }
            else
            {
                 mymsg = "Arguments are not set, please specify directory output (local and remote) and filename by running: application c:\\local-loc\\ phonebook.xml \\\\remote-loc\\directory\\\r\n" +
                    "If not the file will be created in Application's folder with the default name.\r\n";  
                //Console.WriteLine(mymsg);
                mylog = mymsg;
            }

            if (myXmlDirectory != "" && myXmlFilename!="" && myRemoteXmlDirectory!="" ) {
                myXmlLocalOutputFile = myXmlDirectory + "\\" + myXmlFilename;
                myXmlRemoteOutputFile = myRemoteXmlDirectory + "\\"+ myXmlFilename;
                }

            // Connect to dbase

            //string myConnectionString = "Data Source=atlas\\sql2008r2;Initial Catalog=EBSELMETAFORIKH;User ID=*;Password=*";
            /* 
                Get the connection string from configuration file. The string is encrypted. 
                If you change SQL Server or username or password you will need to create a new App.Config 
                for this new connection to dbase and encrypt it with the EncryptDecryptAppConfigFile Application.
                Please download it and compile it to VS2010 or later from here:
                http://www.codeproject.com/Tips/598863/EncryptionplusDecryptionplusConnectionplusStringpl
                You can use the included .exe if you don't want to download and compile the code yourself.
                Please use the App.config.template.txt to create the App.config file and encrypt it.
                The EncryptDecryptAppConfigFile Application must run in machine you wish to create the encrypted file.
             */
            string myConnectionString = ConfigurationManager.ConnectionStrings["SecurePassDataBase"].ToString();
            SqlConnection myConnection =  new SqlConnection (myConnectionString);
            string mySqlText = "";
            
            // STEP1 - Get All Groups
            mySqlText = "select " + 
                "t1.Code as pbgroup_id, "+
                "t1.Description as pbgroup_name "+
                "from ESGOZPersonTable10 t1 "+
                "inner join (select max(fTableField10Code) t1_fTableField10Code from "+
                "ESGOPerson where fTableField10Code is not null group by fTableField10Code) t2 on t1.Code=t2.t1_fTableField10Code";

            // Set the xmlNodes for Croups
            String xmlNodeGroups = "";

            try
            {
                myConnection.Open();
                SqlDataAdapter myDataAdapter = new SqlDataAdapter(mySqlText, myConnection);
                DataSet myDataSet = new DataSet();
                myDataAdapter.Fill(myDataSet);
                myConnection.Close();

                
                // Go through all records in dataset
                foreach (DataRow myRow in myDataSet.Tables[0].Rows)
                {
                    string myGroup = "<pbgroup><id>" + myRow[0].ToString() + "</id><name>" + myRow[1].ToString() + "</name></pbgroup>";
                    xmlNodeGroups = xmlNodeGroups + myGroup;
                } 

            }
            catch (Exception ex)
            {
                mymsg = mymsg + "STEP1 // Something went wrong:" + ex.Message + "\r\n"; 
                Console.WriteLine(mymsg);
                mylog = DateTime.Now.ToString("dd/MM/yy hh:mm:ss") + "\r\n" + mymsg;
                //File.WriteAllText(myLogTxtFile, mylog);
                File.AppendAllText(myLogTxtFile, mylog);
            }
          
            
            // STEP-2 Get All Contacts
            mySqlText = "select "+ 
                    "1 as version,"+ 
                    "t1.fTableField10Code as pbgroup_id,"+ 
                    "t3.Description as pbgroup_name,"+ 
                    "/*t1.fGroupCode,*/"+
                    "case t1.PersonKind "+
                    "when 0 then t1.CommonName "+
                    "else t1.FirstName "+
                    "end as FirstName,"+
                    "/*t1.FirstName,*/"+ 
                    "t1.LastName,"+ 
                    "t2.Telephone1 as Phone_Work,"+ 
                    "t2.Telephone2 as Phone_Home,"+ 
                    "t1.Mobile1 as Phone_Mobile,"+ 
                    "t1.fTableField9Code as Phone_accountindex,"+ 
                    "0 as downloaded "+ 
                    "/*t1.flagfield10*/"+
                    " from ESGOPerson t1"+ 
                    " inner join ESGOSites t2 on t1.fMainAddressGID = t2.GID"+ 
                    " left join ESGOZPersonTable10 t3 on t1.fTableField10Code = t3.Code"+ 
                    " where t1.flagfield10=1";

            // Set the xmlNodes for Contacts
            String xmlNodeContacts = "";
            
            try
            {
                myConnection.Open();
                SqlDataAdapter myDataAdapter = new SqlDataAdapter(mySqlText, myConnection);
                DataSet myDataSet = new DataSet();
                myDataAdapter.Fill(myDataSet);
                myConnection.Close();

                // Go through all records in dataset
                foreach (DataRow myRow in myDataSet.Tables[0].Rows)
                {
                    string myFirstName = "<FirstName>" + myRow[3].ToString() + "</FirstName>";
                    string myLastName = "<LastName>" + myRow[4].ToString() + "</LastName>";
                    string myWorkPhone = "";
                    string myHomePhone = "";
                    string myMobilePhone = "";
                    string myDownloaded = "";
                    string myPBGroup = "";

                    if (myRow[5].ToString() != "") 
                        {
                            myWorkPhone = "<Phone type=\"Work\"><phonenumber>" + RemoveSpecialCharacters(myRow[5].ToString()) + "</phonenumber><accountindex>" + myRow[9] + "</accountindex></Phone>"; //myDataSet.Tables[0].Columns[5].ColumnName; //Get Phone_Work
                        }
                    if (myRow[6].ToString() != "")
                        {
                            myHomePhone = "<Phone type=\"Home\"><phonenumber>" + RemoveSpecialCharacters(myRow[6].ToString()) + "</phonenumber><accountindex>" + myRow[9] + "</accountindex></Phone>"; //myDataSet.Tables[0].Columns[5].ColumnName; //Get Phone_Work
                        }
                    if (myRow[7].ToString() != "")
                        {
                            myMobilePhone = "<Phone type=\"Mobile\"><phonenumber>" + RemoveSpecialCharacters(myRow[7].ToString()) + "</phonenumber><accountindex>" + myRow[9] + "</accountindex></Phone>"; //myDataSet.Tables[0].Columns[5].ColumnName; //Get Phone_Work
                        }

                    myDownloaded = "<downloaded>0</downloaded>";
                    myPBGroup = "<Group>" + myRow[1].ToString() + "</Group>";

                    string myContact = "<Contact>" + myFirstName + myLastName + myWorkPhone + myHomePhone + myMobilePhone + myDownloaded + myPBGroup + "</Contact>";
                    xmlNodeContacts = xmlNodeContacts + myContact;
                    //Console.WriteLine(myWorkPhone + ":" + myRow[5] + " " + myRow[3]);
                    //Console.WriteLine(myContact);
                  

                }
                //Console.WriteLine(xmlNodeContact);
                String xmlAddressBook = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><AddressBook><version>1</version>" + xmlNodeGroups + xmlNodeContacts + "</AddressBook>";
                XmlDocument myXmlDoc = new XmlDocument();
                myXmlDoc.LoadXml(xmlAddressBook);
                myXmlDoc.Save(myXmlLocalOutputFile);
                //ds.WriteXml("mytest.xml");
                // Copy file to remote location
                if (myXmlRemoteOutputFile != "") { File.Copy(myXmlLocalOutputFile, myXmlRemoteOutputFile, true);}

                mymsg = mymsg + "The xml phonebook file was created succesfully.\r\n";
                Console.WriteLine(mymsg);
                mylog = DateTime.Now.ToString ("dd/MM/yy hh:mm:ss") + "\r\n" + mymsg;
                //File.WriteAllText(myLogTxtFile, mylog);
                // Append to log file
                File.AppendAllText(myLogTxtFile, mylog);
            }
            catch (Exception ex)
            {
                mymsg = mymsg + "STEP2 // Something went wrong:" + ex.Message + "\r\n";
                Console.WriteLine(mymsg);
                mylog = DateTime.Now.ToString("dd/MM/yy hh:mm:ss") + "\r\n" + mymsg;
                //File.WriteAllText(myLogTxtFile, mylog);
                File.AppendAllText(myLogTxtFile, mylog);
            }
            //Console.ReadLine();
            
        }

        private static string RemoveSpecialCharacters(string str)
        {
            return Regex.Replace(str, "[^0-9+]+", "", RegexOptions.Compiled);
        }
    }
}
